﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CWCS.DataAccess.DataProvider;

namespace CWCS.DataAccess
{
    public  class DALBase 
    {
        //'SC01','SC02','SC03',
        public const string SQL_SELECT_OPCITEM = "SELECT * FROM WCS_OPCITEM WHERE WAREHOUSEID = :WareHouseID AND STATUS is not null AND AREACODE ='100-LG'  ORDER BY ITEMID";

        public const string SQL_SELECT_WCSEQUIPMENT = @"SELECT W.*,E.QTY FROM WCS_EQUIPMENTINFO w 
LEFT JOIN EQUIPMENT E
ON W.equipmentnum = E.EQUIPMENTNUMBER WHERE W.warehouseid=:WareHouseID and W.zonecode=:ZoneCode  AND W.Protocoltype = 'DAMBACH'  ORDER BY W.equipmentnum,rport";

        public const string SQL_SELECT_TOSTATION = "SELECT WS.FROMSTATION,WS.TOSTATION,WS.CURRENTSTATION,WS.BARCODE,WT.WMSSEQUENCENO,WT.FIELD5 as WEIGHT,WT.WMSGROUPNO FROM WCS_SUBTASKINFO WS INNER JOIN WCS_TASKINFO WT ON WS.TASKNO = WT.TASKNO WHERE  WS.EQUIPMENTTYPE= :EquipmentType AND WS.TASKSTATUS= :TaskStatus  AND WS.TASKNO=:Taskno ";

        public const string SQL_SELECT_EQUIPMENT = "SELECT FIELD1 FROM EQUIPMENT WHERE WAREHOUSEID=:WareHouseID AND EQUIPMENTNUMBER=:EquipmentNumber";

        public const string SQL_SELECT_ITEMCODE = "SELECT DISTINCT ITEMCODE,USERID FROM WCS_ITEMCODE WHERE WAREHOUSEID=:WareHouseID AND TASKNO=:TaskNo";

        public const string SQL_SELECT_LEDTEXT = "SELECT LEDTEXT FROM WCS_TASKINFO WHERE WAREHOUSEID=:WareHouseID AND TASKNO=:TaskNo";

        public const string SQL_SELECT_BARCODE = "SELECT decode(BARCODE,'','11111111'，BARCODE) as BARCODE FROM WCS_TASKINFO WHERE WAREHOUSEID=:WareHouseID AND TASKNO=:TaskNo";
        public const string SQL_SELECT_STATOSTATION = "SELECT FROMSTATION,TOSTATION,CURRENTSTATION,BARCODE,field5 FROM WCS_SUBTASKINFO WHERE WAREHOUSEID=:WareHouseID AND EQUIPMENTTYPE='STA' AND TASKNO=:Taskno AND TASKSTATUS=:Status";
        public const string SQL_SELECT_Tostaion = "SELECT TOSTATION FROM WCS_TASKINFO WHERE WAREHOUSEID=:WareHouseID AND TASKNO=:TaskNo";
        public const string SQL_SELECT_CurrentTask1128 = "select k.taskno from wcs_taskinfo k where k.wmsgroupno in (select t.wmsgroupno from wcs_taskinfo t where t.WAREHOUSEID=:WareHouseID AND t.TASKNO=:TaskNo)";
        //public const string SQL_SELECT_SCAlamCode = "select statecode from equipment where warehouseid =:WareHouseID and equipmentnumber like 'SC%' order by equipmentnumber";
        public const string SQL_SELECT_RFITEMCODE = "SELECT ITEMCODE FROM WCS_ITEMCODE WHERE WAREHOUSEID=:WareHouseID AND RFIDNO IS NOT NULL AND TASKNO='9999' AND USERID='RF'AND RFIDNO=(SELECT MIN(RFIDNO) FROM WCS_ITEMCODE WHERE WAREHOUSEID=:WareHouseID AND RFIDNO IS NOT NULL AND TASKNO='9999' AND USERID='RF')";

        public const string SQL_SELECT_SEND_TASK_AGV = "SELECT a.TASKNO,b.WMSTASKNO,a.FROMSTATION,a.BARCODE,a.TOSTATION,a.EQUIPMENTNUMBER,a.AGVINDEX,a.AGVCARNO,decode(a.TASKSTATUS,'0','未开始','1','已完成','执行中') as TASKSTATUS,"
                                                    + "decode(a.RUNSTATUS,'0','不可执行','可执行') AS RUNSTATUS,a.AGVIKEY,"
                                                    + "decode(b.TASKTYPE,'Task_InBound','入库','Task_OutBound','出库',b.TASKTYPE) AS TASKTYPE,"
                                                    + "TO_CHAR(a.CREATETIME,'YYYY-MM-DD HH24:MI:SS') CREATETIME,TO_CHAR(a.UPDATETIME,'YYYY-MM-DD HH24:MI:SS') UPDATETIME "
                                                    + "FROM WCS_SUBTASKINFO a LEFT JOIN WCS_TASKINFO b ON a.TASKNO = b.TASKNO WHERE a.WAREHOUSEID=:WareHouseID AND a.EQUIPMENTTYPE=:EquipmentType "
                                                    + "AND a.TASKSTATUS='0' and b.TASKTYPE ='Task_AGV' and a.fromstation=:FromStation and a.runstatus='1' AND (a.BARCODE = b.BARCODE or b.BARCODE IS NULL) ORDER BY a.CREATETIME DESC";
        public const string SQL_SELECT_TASKBYSTATION = "SELECT * FROM task WHERE WAREHOUSEID=:WareHouseID AND targetaddresstext=:ToStation";
        public const string SQL_UPDATE_LOTNUMBER = "UPDATE EQUIPMENT E SET E.FIELD1 =:LOTNUMBER,E.FIELD2=:FLAG WHERE E.WAREHOUSEID = :WareHouseID AND E.EQUIPMENTNUMBER =:EquipmentNumber ";
        public const string SQL_SELECT_TASKLOTNUMBER = @"select substr(field2,1,length(field2)-8) as itemcode,wmsbatchno,wmsorderqty,field1 as sequencenumber from wcs_taskinfo t where taskno=:TaskNo And warehouseid =:WareHouseID";
        public const string PARAM_FROMSTATION = "@FromStation";
        public const string PARAM_TOSTATION = "@ToStation";
        public const string PARAM_WAREHOUSE_ID = "@WareHouseID";
        public const string PARAM_ZoneCode = "@ZoneCode";

        public const string PARAM_EQUIPMENTTYPE = "@EquipmentType";
        public const string PARAM_EQUIPMENTNUMBER = "@EquipmentNumber";
        public const string PARAM_TASKNO = "@TaskNo";

        public string WareHouseID = System.Configuration.ConfigurationSettings.AppSettings["WareHouseID"].ToString();
        public string ZoneCode = System.Configuration.ConfigurationSettings.AppSettings["ZoneCode"].ToString();

    }
}
